package com.example.demo.mapper;

import com.example.demo.entity.Order;
import com.example.demo.entity.Order1;
import com.example.demo.entity.Order2;
import com.example.demo.entity.Order3;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface OrderMapper {
    @Insert("insert into orders(CustomerId,BookId,OrderNumber,Money,Date) values(#{CustomerId},#{BookId},#{OrderNumber},#{Money},#{Date})")
    void insertOrder(Order order);

    @Select("select * from orders where CustomerId=#{id}")
    List<Order> selectOrder(String id);

    @Select("select * from orders order by Date desc")
    List<Order> selectO();

    @Delete("delete from orders where OrderId=#{id}")
    void deleteOrder(String id);

    @Select("select sum(Money) as Money,sum(OrderNumber) as OrderNumber from orders")
    Map<String,String> statistics();

    //近五个月的销售情况
    @Select("select Sum(Money) as Money,DATE_FORMAT(Date, '%Y-%m') as Date from orders group by  DATE_FORMAT(Date, '%Y-%m') desc limit 5")
    List<Order3> statistics1();

    @Select("select OrderId,CustomerId,orders.BookId,OrderNumber,Money,BookName,Date from orders join book on orders.BookId=book.BookId order by Date desc")
    List<Order1> selectOrder2();

    @Select("select OrderId,CustomerId,orders.BookId,OrderNumber,Money,BookName,Date from orders join book on orders.BookId=book.BookId where BookName=#{name}")
    List<Order1> selectOrder3(String name);

    @Select("select * from (select count(OrderNumber) as a,TypeName from orders join booktype join book on orders.BookId=book.BookId and book.TypeId=booktype.TypeId group by TypeName) cc where a=(select max(a) as a from (select count(OrderNumber) as a,TypeName from orders join booktype join book on orders.BookId=book.BookId and book.TypeId=booktype.TypeId group by TypeName) cc)")
    Map<String, String> analysis();

    //销量榜前五的图书类型
    @Select("select TypeName,sum(OrderNumber) as OrderNumber from orders join booktype join book on orders.BookId=book.BookId and book.TypeId=booktype.TypeId group by book.TypeId order by OrderNumber desc limit 5")
    List<Order2> analysis1();

    @Select("select OrderId,CustomerId,orders.BookId,OrderNumber,Money,BookName,Date from orders join book on orders.BookId=book.BookId where typeId=#{id}")
    List<Order1> selectOrder4(String id);
}
